﻿Module TaskDao
    Public Sub insert(ByRef task As Task)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Try
            Dim query As String = ""

            query = "INSERT INTO " & task.getTableName & " (name, estimated_start_date, estimated_end_date, observations, task_type_id, proyect_activities_id, state_id, project_member_id)"
            query &= " values('" & task.nameProperty & "'"
            query &= ", '" & task.estimatedStartDateProperty & "'"
            query &= ", '" & task.estimatedEndDateProperty & "'"
            query &= ", '" & task.observationsProperty & "'"
            query &= ", " & task.taskTypeProperty.idProperty & ""
            query &= ", " & task.projectActivityProperty.idProperty & ""
            query &= ", " & task.stateProperty.idProperty & ""
            query &= ", " & task.projectMemberProperty.idProperty & " )"

            command.Connection = connection
            command.CommandType = CommandType.Text
            command.CommandText = query

            command.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Se produjo un error y no se puedieron guardar los cambios.", MsgBoxStyle.Critical)
        End Try


        DataAccessConnection.closeConnection()
    End Sub

    Public Sub update(ByRef task As Task)
        DataAccessConnection.openConnection()
        Dim transaction As OracleClient.OracleTransaction
        Dim command As New OracleClient.OracleCommand

        command.Connection = connection
        command.CommandType = CommandType.Text

        transaction = connection.BeginTransaction()
        command.Transaction = transaction
        Try
            Dim query As String = ""
            query = "update " + task.getTableName + " "
            query &= "   set name                  = :name, "
            query &= "       estimated_start_date  = :estimated_start_date, "
            query &= "       estimated_end_date    = :estimated_end_date, "
            query &= "       observations          = :observations, "
            query &= "       task_type_id          = :task_type_id, "
            query &= "       proyect_activities_id = :proyect_activities_id, "
            query &= "       state_id              = :state_id, "
            query &= "       project_member_id     = :project_member_id "
            query &= " where id = :id "

            command.Connection = connection
            command.CommandType = CommandType.Text
            command.CommandText = query
            command.Parameters.AddWithValue("id", task.idProperty)
            command.Parameters.AddWithValue("name", task.nameProperty)
            command.Parameters.AddWithValue("estimated_start_date", task.estimatedStartDateProperty)
            command.Parameters.AddWithValue("estimated_end_date", task.estimatedEndDateProperty)
            command.Parameters.AddWithValue("observations", task.observationsProperty)
            command.Parameters.AddWithValue("task_type_id", task.taskTypeProperty.idProperty)
            command.Parameters.AddWithValue("proyect_activities_id", task.projectActivityProperty.idProperty)
            command.Parameters.AddWithValue("state_id", task.stateProperty.idProperty)
            command.Parameters.AddWithValue("project_member_id", task.projectMemberProperty.idProperty)
            command.ExecuteNonQuery()

            'Elimino los Avances de la tarea
            Dim queryDeleteProgressTask As String = ""
            Dim taskProgress As TaskProgress = New TaskProgress
            Dim projectActivityObj As New ProjectActivity
            command.Parameters.Clear()
            queryDeleteProgressTask = "  DELETE FROM " + taskProgress.getTableName + " "
            queryDeleteProgressTask &= " WHERE task_id = :task_id "
            command.CommandText = queryDeleteProgressTask
            command.Parameters.AddWithValue("task_id", task.getId)
            command.ExecuteNonQuery()

            'Inserto los Avances de la tarea
            For Each tp As TaskProgress In task.progressTaskProperty
                Dim queryInsertProgressTask As String = ""
                command.Parameters.Clear()
                queryInsertProgressTask = "   INSERT INTO " + taskProgress.getTableName + ""
                queryInsertProgressTask &= "   ( task_progress_date,"
                queryInsertProgressTask &= " 	 start_hour,"
                queryInsertProgressTask &= " 	 end_hour,"
                queryInsertProgressTask &= " 	 observations,"
                queryInsertProgressTask &= " 	 percentage,"
                queryInsertProgressTask &= " 	 task_id)"
                queryInsertProgressTask &= " VALUES"
                queryInsertProgressTask &= " 	('" & Today & "',"
                queryInsertProgressTask &= " 	 '" & tp.startHourProperty & "',"
                queryInsertProgressTask &= " 	 '" & tp.endHourProperty & "',"
                queryInsertProgressTask &= " 	 '" & tp.observationsProperty & "' ,"
                queryInsertProgressTask &= " 	 '" & tp.percentageProperty & "',"
                queryInsertProgressTask &= " 	 '" & task.idProperty & "')"
                command.CommandText = queryInsertProgressTask

                command.ExecuteNonQuery()
            Next
            transaction.Commit()
        Catch e As Exception
            'MsgBox("Error al insertar el projecto. " & descripcionAccion & e.Message)
            MsgBox("Se produjo un error al guardar los cambios.")
            transaction.Rollback()
        Finally
            DataAccessConnection.closeConnection()
        End Try
    End Sub

    Public Sub updateStartDateTask(ByRef task As Task)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = " update " + task.getTableName + " "
        query &= "   set start_date  = :start_date "
        query &= "   where id = :id "

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("id", task.idProperty)
        command.Parameters.AddWithValue("start_date", task.startDateProperty)

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub updateEndDateTask(ByRef task As Task)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = " update " + task.getTableName + " "
        query &= "   set end_date  = :end_date "
        query &= "   where id = :id "

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("id", task.idProperty)
        command.Parameters.AddWithValue("end_date", task.endDateProperty)

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub delete(ByRef task As Task)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""
        query = "delete from " + task.getTableName + " where id = :id"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("id", task.idProperty)

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Function searchDataByFilter(ByRef task As Task) As Data.DataTable
        DataAccessConnection.openConnection()
        Dim com As New OracleClient.OracleCommand
        Dim tab As New DataTable
        Dim query As String

        Dim taskType As New TaskType
        Dim projectActivity As New ProjectActivity
        Dim state As New State
        Dim projectMember As New ProjectMember
        Dim resource As New Resource
        Dim activity As New Activity

        query = ""
        query = "SELECT  t.id as id, "
        query &= "       t.name as name, "
        query &= "       t.estimated_start_date as estimated_start_date, "
        query &= "       t.estimated_end_date as estimated_end_date, "
        query &= "       t.start_date as start_date, "
        query &= "       t.end_date as end_date, "
        query &= "       t.observations as observations, "
        query &= "       t.task_type_id as task_type_id, "
        query &= "       t.proyect_activities_id as proyect_activities_id, "
        query &= "       t.state_id as state_id, "
        query &= "       pm.id as project_member_id, "
        query &= "       ta.name as task_type_name, "
        query &= "       s.name as state_name, "
        query &= "       pm.resource_id as resource_id, "
        query &= "       r.firstname as resoruce_firstname, "
        query &= "       r.surname as resoruce_surname, "
        query &= "       a.id as activity_id, "
        query &= "       a.name as activity_name, "
        query &= "       pa.project_id as project_id "
        query &= "  FROM " & task.getTableName & " t, " & taskType.getTableName & " ta, " & projectActivity.getTableName & " pa, " & state.getTableName & " s, " & projectMember.getTableName & " pm, " & resource.getTableName & " r, " & activity.getTableName & " a "
        query &= "  WHERE (t.id = :id OR :id = 0)"
        query &= "   and t.name like '%" & task.nameProperty & "%'"
        query &= "   and (t.task_type_id = :task_type_id OR :task_type_id = 0)"
        query &= "   and (t.proyect_activities_id = :proyect_activities_id OR :proyect_activities_id = 0)"
        query &= "   and (t.state_id = :state_id OR :state_id = 0)"
        query &= "   and (t.project_member_id = :project_member_id OR :project_member_id = 0)"
        query &= "   and t.task_type_id = ta.id "
        query &= "   and t.project_member_id = pm.id "
        query &= "   and t.proyect_activities_id = pa.id "
        query &= "   and t.state_id = s.id "
        query &= "   and pm.resource_id = r.id "
        query &= "   and pa.activity_id = a.id "

        com.Connection = connection
        com.CommandType = CommandType.Text
        com.CommandText = query
        com.Parameters.Clear()
        com.Parameters.AddWithValue("id", task.idProperty)
        com.Parameters.AddWithValue("task_type_id", If(task.taskTypeProperty Is Nothing, Nothing, task.taskTypeProperty.idProperty))
        com.Parameters.AddWithValue("proyect_activities_id", If(task.projectActivityProperty Is Nothing, Nothing, task.projectActivityProperty.idProperty))
        com.Parameters.AddWithValue("state_id", If(task.stateProperty Is Nothing, Nothing, task.stateProperty.idProperty))
        com.Parameters.AddWithValue("project_member_id", If(task.projectMemberProperty Is Nothing, Nothing, task.projectMemberProperty.idProperty))

        tab.Load(com.ExecuteReader)
        DataAccessConnection.closeConnection()
        Return tab
    End Function

    Public Function searchDataByFilterToSearchForm(ByRef task As Task) As Data.DataTable
        DataAccessConnection.openConnection()
        Dim com As New OracleClient.OracleCommand
        Dim tab As New DataTable
        Dim query As String

        Dim taskType As New TaskType
        Dim projectActivity As New ProjectActivity
        Dim state As New State
        Dim projectMember As New ProjectMember
        Dim resource As New Resource
        Dim activity As New Activity
        Dim project As New Project

        query = ""
        query = "SELECT  t.id as t_id, "
        query &= "       t.name as t_name, "
        query &= "       ta.name as task_type_name, "
        query &= "       r.surname || ', ' || r.firstname as resoruceName, "
        query &= "       t.estimated_start_date as estimated_start_date, "
        query &= "       t.estimated_end_date as estimated_end_date, "
        query &= "       a.name as activity_name, "
        query &= "       p.name as project_name, "
        query &= "       s.name as state_name "
        query &= "  FROM " & task.getTableName & " t, " & taskType.getTableName & " ta, " & projectActivity.getTableName & " pa, " & state.getTableName & " s, " & projectMember.getTableName & " pm, " & resource.getTableName & " r, " & activity.getTableName & " a, " & project.getTableName & " p"
        query &= "  WHERE (t.id = :id OR :id = 0)"
        query &= "   and  t.name like '%" & task.nameProperty & "%'"
        query &= "   and (t.task_type_id = :task_type_id OR :task_type_id = 0)"
        query &= "   and (t.proyect_activities_id = :proyect_activities_id OR :proyect_activities_id = 0)"
        query &= "   and (t.state_id = :state_id OR :state_id = 0)"
        query &= "   and (t.project_member_id = :project_member_id OR :project_member_id = 0)"
        query &= "   and t.task_type_id = ta.id "
        query &= "   and t.project_member_id = pm.id "
        query &= "   and t.proyect_activities_id = pa.id "
        query &= "   and t.state_id = s.id "
        query &= "   and pm.resource_id = r.id "
        query &= "   and pa.activity_id = a.id "
        query &= "   and pa.project_id = p.id "

        com.Connection = connection
        com.CommandType = CommandType.Text
        com.CommandText = query
        com.Parameters.Clear()
        com.Parameters.AddWithValue("id", task.idProperty)
        com.Parameters.AddWithValue("task_type_id", If(task.taskTypeProperty Is Nothing, Nothing, task.taskTypeProperty.idProperty))
        com.Parameters.AddWithValue("proyect_activities_id", If(task.projectActivityProperty Is Nothing, Nothing, task.projectActivityProperty.idProperty))
        com.Parameters.AddWithValue("state_id", If(task.stateProperty Is Nothing, Nothing, task.stateProperty.idProperty))
        com.Parameters.AddWithValue("project_member_id", If(task.projectMemberProperty Is Nothing, Nothing, task.projectMemberProperty.idProperty))

        tab.Load(com.ExecuteReader)
        DataAccessConnection.closeConnection()
        Return tab
    End Function

    Public Function searchDataById(ByRef id As Integer) As Task
        Dim table As New Data.DataTable
        Dim task As New Task(id)

        table = searchDataByFilter(task)
        task = convertTableToTask(table)
        Return task
    End Function

    Private Function convertTableToTask(ByRef table As Data.DataTable) As Task
        Dim task As New Task()
        Dim taskType As New TaskType()
        Dim state As New State()
        Dim projectMember As New ProjectMember()
        Dim projectActivity As New ProjectActivity()
        Dim resource As New Resource()
        Dim activity As New Activity()
        Dim project As New Project(table.Rows(0)("project_id"))

        Dim list_progress_tasks As New ArrayList

        task.idProperty = table.Rows(0)("id")
        task.nameProperty = table.Rows(0)("name")
        task.estimatedStartDateProperty = table.Rows(0)("estimated_start_date")
        task.estimatedEndDateProperty = table.Rows(0)("estimated_end_date")
        task.startDateProperty = IIf(IsDBNull(table.Rows(0)("start_date")), Nothing, table.Rows(0)("start_date"))
        task.endDateProperty = IIf(IsDBNull(table.Rows(0)("end_date")), Nothing, table.Rows(0)("end_date"))
        task.observationsProperty = IIf(IsDBNull(table.Rows(0)("observations")), Nothing, table.Rows(0)("observations"))

        taskType.idProperty = table.Rows(0)("task_type_id")
        taskType.nameProperty = table.Rows(0)("task_type_name")
        task.taskTypeProperty = taskType

        state.idProperty = table.Rows(0)("state_id")
        state.nameProperty = table.Rows(0)("state_name")
        task.stateProperty = state



        projectMember.idProperty = table.Rows(0)("project_member_id")
        resource.idProperty = table.Rows(0)("resource_id")
        resource.firstnameProperty = table.Rows(0)("resoruce_firstname")
        resource.surnameProperty = table.Rows(0)("resoruce_surname")
        projectMember.resourceProperty = resource
        projectMember.projectProperty = project
        task.projectMemberProperty = projectMember

        projectActivity.idProperty = table.Rows(0)("proyect_activities_id")
        activity.idProperty = table.Rows(0)("activity_id")
        activity.nameProperty = table.Rows(0)("activity_name")
        projectActivity.activityProperty() = activity
        projectActivity.projectProperty = project
        task.projectActivityProperty = projectActivity

        Dim progressTasks As New TaskProgress(task)
        list_progress_tasks = TaskProgressDao.searchData(progressTasks)
        task.progressTaskProperty = list_progress_tasks

        Return task
    End Function
End Module
